package servlet.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import servlet.bean.CourseBean;
import servlet.bean.TeacherBean;
import servlet.bean.UserBean;
import servlet.dbconnect.DBDriverFactory;

public class UserInfoManagerDao {
	
public int saveStuInfo(TeacherBean teaB) {
		
		int re_i = 0;
		if(teaB.getStu_num()==null){
			//新增处理
			String strSql = "INSERT INTO TBUSERSTU(STU_ID ,STU_NAME ,STU_COURSE,STU_RECORD)VALUES(?,?,?,?)";
			re_i = DBDriverFactory.getDBDriverInstance().update(strSql,new Object[]{teaB.getStu_id(),teaB.getStu_name(),teaB.getStu_course(),teaB.getStu_record()});
		}else{
			//修改处理
			String strsql = "UPDATE TBUSERSTU SET STU_ID=?, STU_NAME=? , STU_COURSE=? , STU_RECORD=? WHERE STU_NUM= ?";
			re_i = DBDriverFactory.getDBDriverInstance().update(strsql,new Object[]{teaB.getStu_id(),teaB.getStu_name(),teaB.getStu_course(),teaB.getStu_record(),teaB.getStu_num()});
		}
		return re_i;
	}

public int saveCouInfo(CourseBean couB) {
	
	int re_i = 0;
	if(couB.getCou_num()==null){
		//新增处理
		String strSql = "INSERT INTO TBUSERCOU(COU_ID ,COU_NAME )VALUES(?,?)";
		re_i = DBDriverFactory.getDBDriverInstance().update(strSql,new Object[]{couB.getCou_id(),couB.getCou_name()});
	}else{
		//修改处理
		String strsql = "UPDATE TBUSERCOU SET COU_ID= ? ,COU_NAME=? WHERE COU_NUM= ?";
		re_i = DBDriverFactory.getDBDriverInstance().update(strsql,new Object[]{couB.getCou_id(),couB.getCou_name(),couB.getCou_num()});
	}
	return re_i;
}

public int saveStuBatch(TeacherBean teaB) {
	
	int re_i = 0;
	
		//修改处理
		String strsql = "UPDATE TBUSERSTU SET  STU_RECORD=? WHERE STU_NUM= ?";
		re_i = DBDriverFactory.getDBDriverInstance().update(strsql,new Object[]{teaB.getStu_record(),teaB.getStu_num()});
	
	return re_i;
}
	
	public int saveUserInfo(UserBean userB) {
		
		int re_i = 0;
		if(userB.getUser_id()==null){
			//新增处理
			String strSql = "INSERT INTO TBUSER1(USERNAME ,USER_SEX ,USER_AGE,USER_PSW ,USER_PHONE ,USER_ADDRESS,USER_ROLE)VALUES(?,?,?,?,?,?,?)";
			re_i = DBDriverFactory.getDBDriverInstance().update(strSql,new Object[]{userB.getUsername(),userB.getUser_sex(),userB.getUser_age(),userB.getUser_psw(),userB.getUser_phone(),userB.getUser_address(),userB.getUser_role()});
		}else{
			//修改处理
			String strsql = "UPDATE TBUSER1 SET USERNAME=? , USER_SEX=? , USER_AGE=? , USER_PSW=? , USER_PHONE=? , USER_ADDRESS=? , USER_ROLE=? WHERE USER_ID= ?";
			re_i = DBDriverFactory.getDBDriverInstance().update(strsql,new Object[]{userB.getUsername(),userB.getUser_sex(),userB.getUser_age(),userB.getUser_psw(),userB.getUser_phone(),userB.getUser_address(),userB.getUser_role(),userB.getUser_id()});
		}
		return re_i;
	}
	
	
	public List<TeacherBean> queryStuInfo2(TeacherBean teaB){
		String strSql="SELECT S.STU_COURSE , S.STU_RECORD FROM TBUSERSTU S, TBCHOOSECOU C WHERE C.STU_ID=S.STU_ID AND S.STU_ID = ?  GROUP BY S.STU_COURSE , S.STU_RECORD ";
		List<TeacherBean> lstUser = DBDriverFactory.getDBDriverInstance().query(strSql,new BeanListHandler<TeacherBean>(TeacherBean.class),new Object[]{teaB.getStu_id()});
		return lstUser;
	}
	
	
	public List<TeacherBean> queryStuInfo(TeacherBean teaB){
		StringBuilder sbSql= new StringBuilder("SELECT * FROM TBUSERSTU WHERE 1=1 ");
		List<Object> lstParas= new ArrayList<Object>();
		if(teaB.getStu_id()!=null&& !teaB.getStu_id().equals("")){
			sbSql.append("AND STU_ID LIKE ? ");
			lstParas.add("%%"+teaB.getStu_id()+"%%");
		}
		if(teaB.getStu_name()!=null&& !teaB.getStu_name().trim().equals("")){
			sbSql.append("AND STU_NAME LIKE ? ");
			lstParas.add("%%"+teaB.getStu_name()+"%%");
		}
		
		if(teaB.getStu_course()!=null&& !teaB.getStu_course().trim().equals("")){
			sbSql.append("AND STU_COURSE LIKE ? ");
			lstParas.add("%%"+teaB.getStu_course()+"%%");
		}
		
		if(teaB.getStu_record()>0){
			sbSql.append("AND STU_RECORD >= ? ");
			lstParas.add(teaB.getStu_record());
		}
		if(teaB.getStu_record2()>0){
			sbSql.append("AND STU_RECORD <= ? ");
			lstParas.add(teaB.getStu_record2());
		}
		sbSql.append(" ORDER BY STU_ID  ");
		
		List<TeacherBean> lstStu = DBDriverFactory.getDBDriverInstance().query(sbSql.toString(),new BeanListHandler<TeacherBean>(TeacherBean.class), lstParas.toArray());
		return lstStu;
	}
	
	
	
	public List<UserBean> queryUserInfo(UserBean userB){
		StringBuilder sbSql= new StringBuilder("SELECT * FROM TBUSER1 WHERE 1=1 ");
		List<Object> lstParas= new ArrayList<Object>();
		if(userB.getUsername()!=null&& !userB.getUsername().trim().equals("")){
			sbSql.append("AND USERNAME LIKE ? ");
			lstParas.add("%%"+userB.getUsername()+"%%");
		}
		if(userB.getUser_sex()!=null&& !userB.getUser_sex().trim().equals("")){
			sbSql.append("AND USER_SEX = ?");
			lstParas.add(userB.getUser_sex());
		}
		if(userB.getUser_age()>0){
			sbSql.append("AND USER_AGE >= ? ");
			lstParas.add(userB.getUser_age());
		}
		if(userB.getUser_age2()>0){
			sbSql.append("AND USER_AGE <= ? ");
			lstParas.add(userB.getUser_age2());
		}
		if(userB.getUser_address()!=null&& !userB.getUser_address().trim().equals("")){
			sbSql.append("AND USER_ADDRESS LIKE ? ");
			lstParas.add("%%"+userB.getUser_address()+"%%");
		}
		if(userB.getUser_phone()!=null&& !userB.getUser_phone().trim().equals("")){
			sbSql.append("AND USER_PHONE LIKE ? ");
			lstParas.add("%%"+userB.getUser_phone()+"%%");
		}
		sbSql.append(" ORDER BY USER_ID ");
		
		List<UserBean> lstUser = DBDriverFactory.getDBDriverInstance().query(sbSql.toString(),new BeanListHandler<UserBean>(UserBean.class), lstParas.toArray());
		return lstUser;
	}
	
	public int deleteUserById(int userId) {
		int re_i = 0;
		// 执行的sql语句
		String strSql = "DELETE FROM TBUSER1 WHERE USER_ID=?";
		// 删除数据
		re_i = DBDriverFactory.getDBDriverInstance().update(strSql, new Object[] { userId });
		return re_i;
	}
	
	public int deleteStuById(int stuId) {
		int re_i = 0;
		// 执行的sql语句
		String strSql = "DELETE FROM TBUSERSTU WHERE STU_NUM=?";
		// 删除数据
		re_i = DBDriverFactory.getDBDriverInstance().update(strSql, new Object[] { stuId });
		return re_i;
	}
	
	public int deleteCouById(int couId) {
		int re_i = 0;
		// 执行的sql语句
		String strSql = "DELETE FROM TBUSERCOU WHERE COU_NUM=?";
		// 删除数据
		re_i = DBDriverFactory.getDBDriverInstance().update(strSql, new Object[] { couId });
		return re_i;
	}
	
	
	public List<UserBean> getAllUserInfo(){
		String strSql="SELECT * FROM TBUSER1 ORDER BY USER_ID DESC";
		List<UserBean> lstUser = DBDriverFactory.getDBDriverInstance().query(strSql,new BeanListHandler<UserBean>(UserBean.class));
		return lstUser;
	}
	
	public List<TeacherBean> getAllStuInfo(){
		String strSql="SELECT * FROM TBUSERSTU ORDER BY STU_ID ";
		List<TeacherBean> lstStu = DBDriverFactory.getDBDriverInstance().query(strSql,new BeanListHandler<TeacherBean>(TeacherBean.class));
		return lstStu;
	}
	//打印所有课程
	public List<CourseBean> getCouInfo(){
		String strSql="SELECT DISTINCT COU_ID , COU_NAME FROM TBUSERCOU GROUP BY COU_ID , COU_NAME ORDER BY COU_ID ";
		
		List<CourseBean> lstCou = DBDriverFactory.getDBDriverInstance().query(strSql,new BeanListHandler<CourseBean>(CourseBean.class));
		return lstCou;
	}
	
	public List<CourseBean> getCou(){
		String strSql="SELECT  * FROM TBUSERCOU ORDER BY COU_ID ";
		
		List<CourseBean> lstCou = DBDriverFactory.getDBDriverInstance().query(strSql,new BeanListHandler<CourseBean>(CourseBean.class));
		return lstCou;
	}
	
	public int delCouInfo(){
		String strSql="DELETE FROM TBCHOOSECOU WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM TBCHOOSECOU GROUP BY COU_ID,STU_ID)";
		int re_i = DBDriverFactory.getDBDriverInstance().update(strSql, null);
		return re_i;
	}
	
	
	public int selectCou(CourseBean couB,String strUid) {
		// 学生选课
        String strSql = "INSERT INTO TBCHOOSECOU(COU_ID,STU_ID) VALUES (?,?)  ";
        int courB = DBDriverFactory.getDBDriverInstance().update(strSql, new Object[] { strUid,couB.getStu_id() });
		return courB;
	}
	
	public UserBean getUserInfoById(String strUid) {
		// 查询数据库的sql语句
        String strSql = "SELECT * FROM TBUSER1 WHERE USER_ID=?";
		UserBean UserB = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanHandler<UserBean>(UserBean.class), new Object[] { strUid });
		return UserB;
	}
	
	public TeacherBean getStuInfoById(String strUid) {
		// 查询数据库的sql语句
        String strSql = "SELECT * FROM TBUSERSTU WHERE STU_NUM=?";
        TeacherBean TeaB = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanHandler<TeacherBean>(TeacherBean.class), new Object[] { strUid });
		return TeaB;
	}
	
	public CourseBean getCouInfoById(String strCouid) {
		// 查询数据库的sql语句
        String strSql = "SELECT * FROM TBUSERCOU WHERE COU_NUM=?";
        CourseBean couB = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanHandler<CourseBean>(CourseBean.class), new Object[] { strCouid });
		return couB;
	}
}
